var express = require('express');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var User = require('./User');
var app = express();
const session = require('express-session');
app.use(session({
    secret: '123456',
    resave: false,
    saveUninitialized: true
}));

// 配置静态资源目录
app.use('/node_modules/', express.static('./node_modules/'));
app.use('/public', express.static('./public/'));

// 配置模板引擎
app.engine('html', require('express-art-template'));
// 解析URL编码的请求体
app.use(bodyParser.urlencoded({ extended: false }));
// 解析JSON格式的请求体
app.use(bodyParser.json());

// 创建数据库连接
var connection = mysql.createConnection({
    host: 'localhost',
    port: '3307',
    user: 'root',
    password: '123456',
    database: 'nodejs'
});
connection.connect();

// 定义学生模型相关操作
var Student = {};
Student.find = function (callback) {
    connection.query(
        `SELECT * 
            FROM student
        `,
        function (err, result) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return callback(err);
            }
            callback(null, result);
        });
};

Student.findOneByName = function (name, callback) {
    connection.query(
        `SELECT *
            FROM student
         WHERE 
            name like "%${name}%"
        `,
        function (err, result) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return callback(err);
            }
            callback(null, result);
        });
};

// 新增学生
var addSql = 'INSERT INTO student(Id,name,gender,age,hobbies) VALUES(NULL,?,?,?,?)';
Student.add = function (student, callback) {
    var addSqlParams = [];
    // 将学生中的属性添加到SQL值数组中
    for (var key in student) {
        addSqlParams.push(student[key]);
    }
    connection.query(addSql, addSqlParams, function (err, result) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            return callback(err);
        }
        callback(null, result);
    });
};

Student.findById = function (id, callback) {
    connection.query(
        `SELECT *
            FROM student
         WHERE 
            id = "${id}"
        `,
        function (err, result) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
                return callback(err);
            }
            callback(null, result[0]);
        });
};

var updateSql = 'UPDATE student SET ' +
    'name =?, gender =?, age =?, hobbies =? WHERE id =?';
Student.findByIdAndUpdate = function (id, student, callback) {
    var addSqlParams = [];
    // 将学生中的属性添加到SQL值数组中
    for (var key in student) {
        addSqlParams.push(student[key]);
    }
    addSqlParams.shift();
    addSqlParams.push(id);
    console.log(addSqlParams);
    connection.query(updateSql, addSqlParams, function (err, result) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            return callback(err);
        }
        callback(null, result.affectedRows);
    });
};

Student.findByIdAndRemove = function (id, callback) {
    var delSql = `DELETE FROM student where id= ${id}`;
    connection.query(delSql, function (err, result) {
        if (err) {
            return callback(err);
        }
        return callback(null, result.affectedRows);
    });
};

// 定义路由
var router = express.Router();

// 处理根路径请求，重定向到登录注册页面
app.get('/', function (req, res) {
    res.redirect('/login_register');
});

// 学生信息查询路由（支持根据姓名搜索和查询所有学生信息）
router.get('/students', requireLogin, function (req, res) {
    if (req.query.name!= null && req.query.name!== '') {
        Student.findOneByName(req.query.name, function (err, students) {
            if (err) {
                return res.status(500).send('Server error');
            }
            console.log(students.toString());
            if (students.toString().length === 0) {
                res.render('index.html', {
                    count: 0,
                });
            } else {
                res.render('index.html', {
                    students: students
                });
            }
        });
    } else {
        Student.find(function (err, students) {
            if (err) {
                return res.status(500).send('Server error');
            }
            if (students.length >= 6) {
                var top = [
                    students[0],
                    students[1],
                    students[2],
                    students[3],
                    students[4],
                    students[5],
                ];
            }
            res.render('index.html', {  // 这里修改为渲染index.html展示学生信息
                top: top,
                students: students
            });
        });
    }
});

// 渲染添加学生页面路由
router.get('/students/new', function (req, res) {
    res.render('new.html');
});

// 处理添加学生请求路由
router.post('/students/new', function (req, res) {
    Student.add(req.body, function (err) {
        if (err) {
            return res.status(500).send('Server error');
        }
        res.redirect('/students');
    });
});

// 渲染编辑学生页面路由
router.get('/students/edit', function (req, res) {
    Student.findById(req.query.id, function (err, student) {
        if (err) {
            console.log(err);
            return res.status(500).send('Server error.');
        }
        res.render('edit.html', {
            student: student
        });
    });
});

// 处理编辑学生请求路由
router.post('/students/edit', function (req, res) {
    var id = req.body.id;
    Student.findByIdAndUpdate(id, req.body, function (err) {
        if (err) {
            return res.status(500).send('Server error.');
        }
        res.redirect('/students');
    });
});

// 处理删除学生请求路由
router.get('/students/delete', function (req, res) {
    var id = req.query.id;
    Student.findByIdAndRemove(id, function (err) {
        if (err) {
            return res.status(500).send('Server error.');
        }
        res.redirect('/students');
    });
});

// 渲染登录注册页面
router.get('/login_register', function (req, res) {
    res.render('login_register.html');
});

// 处理登录请求
router.post('/login', function (req, res) {
    const { username, password } = req.body;
    // 调用用户模型的登录验证方法（假设已创建User模型并实现login方法）
    User.login(username, password, function (err, isLoggedIn) {
        if (err) {
            return res.status(500).send('Server error');
        }
        if (isLoggedIn) {
            // 登录成功，设置会话信息（假设已配置express-session中间件）
            req.session.user = { username };
            res.redirect('/students');
        } else {
            res.send('用户名或密码错误');
        }
    });
});

// 处理注册请求
router.post('/register', function (req, res) {
    const { username, password } = req.body;
    User.register({ username, password }, function (err) {
        if (err) {
            return res.status(500).send('Server error');
        }
        res.redirect('/login_register');
    });
});

// 登录验证中间件
function requireLogin(req, res, next) {
    if (req.session.user) {
        next();
    } else {
        res.redirect('/login_register');
    }
}

app.use(router);

app.listen(8888, function () {
    console.log('服务器运行在 http://localhost:8888');
});